package yiguan;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import org.apache.log4j.Logger;

public class Task {
	private String taskid_;
	private String cid_;
	private String cname_;
	private String ctype_;
	private String csex_;
	private String cservice_;
	private String sname_;
	private String smobile_;
	private String company_;
	private String state_;
	private int consume_;
	private int cash_;

	private static Logger logger = Logger.getLogger("Task");


	public Task(int taskid) {
		this(String.valueOf(taskid));
	}

	public Task(String taskid) {
		taskid_ = taskid;
	}

	public boolean assign(Waiter waiter, String ordertime, String assigner) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("update tasks set sname=?,smobile=?,comid=?," +
						"assigntime=current timestamp,state='1',ordertime=?,assigner=? where taskid=?");
			ps.setString(1, waiter.getWname());
			ps.setString(2, waiter.getMobile());
			ps.setString(3, waiter.getComid());
			ps.setString(4, ordertime);
			ps.setString(5, assigner);
			ps.setString(6, taskid_);
			int rownum = ps.executeUpdate();
			if (rownum == 1)
				retval = true;
			logger.info("task #" + taskid_ + " assigned: " + waiter.getWname());
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	//state = 2 means pickuped
	public void setPickupState() {
		int rownum = DBUtil.executeUpdate("update tasks set picktime=current timestamp, state='2' where taskid=" + taskid_);
		if (rownum == 1)
			logger.info("task #" + taskid_ + " pickuped.");
	}

	//state = 3 means finished
	public void setFinishState(Connection conn, int consume, int cash) { //incoming connection means it is in transaction
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement("update tasks set consume=?,cash=?,finishtime=current timestamp,state='3' where taskid=?");
			ps.setInt(1, consume);
			ps.setInt(2, cash);
			ps.setString(3, taskid_);
			int rownum = ps.executeUpdate();
			logger.info("task #" + taskid_ + " finish: " + rownum);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
		}
	}

	//state = 4 means withdrawed
	public void setWithdrawState(Connection conn) {
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement("update tasks set state='4' where taskid=?");
			ps.setString(1, taskid_);
			int rownum = ps.executeUpdate();
			logger.info("task #" + taskid_ + " withdrawed");
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
		}
	}
	
	//state = 5 means end or closed
	public static int setEndState(String taskid) {
		int rownum = DBUtil.executeUpdate("update tasks set state='5' where taskid='" + taskid + "'");

		if (rownum == 1)
			logger.info("task #" + taskid + " closed.");
		
		return rownum;
	}

	public static JSONObject getAllTask(Hashtable params, int page) {
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		JSONArray tasks = new JSONArray();
		int total;
		int offset = page * 20 - 20;
		JSONObject retval = new JSONObject();
		String sqlWhere = DBUtil.buildSqlWhere(params);

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from tasks" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			ps = conn.prepareStatement("select * from tasks" + sqlWhere + " order by taskid desc offset ? rows fetch next 20 rows only");
			ps.setInt(1, offset);
			rs = ps.executeQuery();
			while(rs.next()) {
				JSONObject task = new JSONObject();
				task.put("id", rs.getString("taskid"));
				String[] ss = new String[21];
				ss[0] = rs.getString("taskid");
				ss[1] = rs.getString("cid").trim();
				ss[2] = rs.getString("cname").trim();
				ss[3] = rs.getString("csex");
				ss[4] = rs.getString("ctype");
				ss[5] = rs.getString("cservice");
				ss[6] = rs.getString("note") == null? "":rs.getString("note").trim();
				ss[7] = rs.getString("ordertime") == null? "":rs.getString("ordertime");
				ss[8] = rs.getString("cmobile") == null? "":rs.getString("cmobile");
				ss[9] = rs.getString("ctel") == null? "":rs.getString("ctel").trim();
				ss[10] = rs.getString("caddress").trim();
				ss[11] = DBUtil.TIME_FORMAT.format(rs.getTimestamp("rectime"));
				ss[12] = rs.getString("comid") == null? "":rs.getString("comid");
				ss[13] = rs.getString("sname") == null? "":rs.getString("sname").trim();
				ss[14] = rs.getString("smobile") == null? "":rs.getString("smobile").trim();
				ss[15] = rs.getTimestamp("assigntime") == null? "":DBUtil.TIME_FORMAT.format(rs.getTimestamp("assigntime"));
				ss[16] = rs.getTimestamp("picktime") == null? "":DBUtil.TIME_FORMAT.format(rs.getTimestamp("picktime"));
				ss[17] = rs.getTimestamp("finishtime") == null? "":DBUtil.TIME_FORMAT.format(rs.getTimestamp("finishtime"));
				ss[18] = rs.getString("state");
				ss[19] = rs.getString("recorder");
				ss[20] = rs.getString("assigner");
				task.put("cell", ss);
				tasks.put(task);
			}

			retval.put("page", page);
			retval.put("total",(total / 20) + 1);
			retval.put("records", total);
			retval.put("rows", tasks);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	// tasks assigned for a waiter, return to mobile
	public static JSONArray getAssignedTaskList(String smobile) {
		JSONArray tasks = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select * from tasks where state in ('1','2','4') and smobile=?");
			ps.setString(1, smobile);
			rs = ps.executeQuery();
			while(rs.next()) {
				JSONObject task = new JSONObject();
				task.put("taskid", rs.getString("taskid"));
				task.put("cid", rs.getString("cid").trim());
				task.put("cname", rs.getString("cname").trim());
				task.put("csex", rs.getString("csex"));
				task.put("ctype", rs.getString("ctype"));
				task.put("cservice", rs.getString("cservice"));
				task.put("note", rs.getString("note").trim());
				task.put("ordertime", rs.getString("ordertime"));
				task.put("cmobile", rs.getString("cmobile").trim());
				task.put("ctel", rs.getString("ctel").trim());
				task.put("state", rs.getString("state"));
				task.put("caddress", rs.getString("caddress").trim());
				task.put("rectime", DBUtil.TIME_FORMAT.format(rs.getTimestamp("rectime")));
				task.put("assigntime", DBUtil.TIME_FORMAT.format(rs.getTimestamp("assigntime")));
				tasks.put(task);
			}
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return tasks;
	}

	// tasks assigned for a waiter, return to jqgrid
	public static JSONObject getAssignedTaskGrid(String smobile, int page) {
		JSONArray tasks = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total;
		int offset = page * 10 - 10;
		JSONObject retval = new JSONObject();

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select count(*) from tasks where smobile=?");
			ps.setString(1, smobile);
			rs = ps.executeQuery();
			rs.next();
			total = rs.getInt(1);
			ps = conn.prepareStatement("select * from tasks where smobile=? order by taskid desc offset ? rows fetch next 10 rows only");
			ps.setString(1, smobile);
			ps.setInt(2, offset);
			rs = ps.executeQuery();
			tasks = getDataFromRS(rs);

			retval.put("page", page);
			retval.put("total",(total / 10) + 1);
			retval.put("records", total);
			retval.put("rows", tasks);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	//tasks belong to a customer, return to jqgrid
	public static JSONObject getCustomerTaskGrid(String cid, int page) {
		JSONArray tasks = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total;
		int offset = page * 10 - 10;
		JSONObject retval = new JSONObject();

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select count(*) from tasks where cid=?");
			ps.setString(1, cid);
			rs = ps.executeQuery();
			rs.next();
			total = rs.getInt(1);
			ps = conn.prepareStatement("select * from tasks where cid=? order by taskid desc offset ? rows fetch next 10 rows only");
			ps.setString(1, cid);
			ps.setInt(2, offset);
			rs = ps.executeQuery();
			tasks = getDataFromRS(rs);

			retval.put("page", page);
			retval.put("total",(total / 10) + 1);
			retval.put("records", total);
			retval.put("rows", tasks);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	private static JSONArray getDataFromRS(ResultSet rs) {
		JSONArray tasks = new JSONArray();

		try {
			while(rs.next()) {
				JSONObject task = new JSONObject();
				String[] ss = new String[11];
				ss[0] = rs.getString("taskid");
				ss[1] = rs.getString("cid").trim();
				ss[2] = rs.getString("cname").trim();
				ss[3] = rs.getString("csex");
				ss[4] = rs.getString("ctype");
				ss[5] = rs.getString("cservice");
				ss[6] = rs.getString("note") == null? "" : rs.getString("note");
				ss[7] = rs.getString("ordertime") == null? "":rs.getString("ordertime");
				ss[8] = rs.getString("state");
				ss[9] = DBUtil.TIME_FORMAT.format(rs.getTimestamp("rectime"));
				ss[10] = rs.getTimestamp("assigntime") == null? "":DBUtil.TIME_FORMAT.format(rs.getTimestamp("assigntime"));
				task.put("id", rs.getString("taskid"));
				task.put("cell", ss);
				tasks.put(task);
			}
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			return null;
		}

		return tasks;
	}

	public static Task getAssignedTaskByCustomer(String mobile, String cid, int taskid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Task task = null;
		String smobile1, cid1, state1;

		try {
			conn = DBUtil.openConnection();
			//state=1 means assigned, state=4 mean withdrawed.
			ps = conn.prepareStatement("select * from tasks where taskid=?");
			ps.setInt(1, taskid);
			rs = ps.executeQuery();
			if (rs.next()) {
				task = new Task(rs.getInt("taskid"));
				smobile1 = rs.getString("smobile").trim();
				cid1 = rs.getString("cid").trim();
				state1 = rs.getString("state");
			} else {
				return null;
			}

			if (!smobile1.equals(mobile.trim()) || !cid1.equals(cid.trim())){
				return null;
			}
			if (!state1.equals("1") && !state1.equals("4")){
				return null;
			}
		} catch (SQLException e) {
			task = null;
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return task;
	}

	public static Task getPickupedTaskByCustomer(String mobile, String cid, int taskid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Task task = null;
		String smobile1, cid1, state1;

		try {
			conn = DBUtil.openConnection();
			//state=2 means pickuped, state=4 means withdrawed.
			ps = conn.prepareStatement("select * from tasks where taskid=? and state in ('2','4')");
			ps.setInt(1, taskid);
			rs = ps.executeQuery();
			if (rs.next()) {
				task = new Task(rs.getInt("taskid"));
				task.cid_ = rs.getString("cid").trim();
				task.cname_ = rs.getString("cname").trim();
				task.ctype_ = rs.getString("ctype");
				task.csex_ = rs.getString("csex");
				task.cservice_ = rs.getString("cservice");
				task.sname_ = (rs.getString("sname") == null? "":rs.getString("sname").trim());
				task.smobile_ = (rs.getString("smobile") == null? "":rs.getString("smobile").trim());
				task.company_ = (rs.getString("comid") == null? "":rs.getString("comid"));
				task.state_ = rs.getString("state");

				if (!task.getCid().equals(cid) || !task.getSmobile().equals(mobile))
					return null;
				if (!task.getState().equals("2") && !task.getState().equals("4"))
					return null;
			}
		} catch (SQLException e) {
			task = null;
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return task;
	}

	public static Task getFinishedTaskByID(String taskid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Task task = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select * from tasks where taskid=? and state='3'"); //state=3 means finished
			ps.setString(1, taskid);
			rs = ps.executeQuery();
			if (rs.next()) {
				task = new Task(taskid);
				task.cid_ = rs.getString("cid").trim();
				task.cname_ = rs.getString("cname").trim();
				task.ctype_ = rs.getString("ctype");
				task.csex_ = rs.getString("csex");
				task.cservice_ = rs.getString("cservice");
				task.sname_ = (rs.getString("sname") == null? "":rs.getString("sname").trim());
				task.smobile_ = (rs.getString("smobile") == null? "":rs.getString("smobile").trim());
				task.company_ = (rs.getString("comid") == null? "":rs.getString("comid"));
				task.consume_ = rs.getInt("consume");
				task.cash_ = rs.getInt("cash");
			}
		} catch (SQLException e) {
			task = null;
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return task;
	}

	public static boolean addTask(String cid, String cname, String csex, String ctype, String cservice,
				String note, String ordertime, String cmobile, String ctel, String caddress, String recorder) {
		boolean retval = false;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("insert into tasks(taskid,cid,cname,csex,ctype,cservice,note,ordertime," +
								"cmobile,ctel,caddress,recorder) values(default,?,?,?,?,?,?,?,?,?,?,?)");
			ps.setString(1, cid);
			ps.setString(2, cname);
			ps.setString(3, csex);
			ps.setString(4, ctype);
			ps.setString(5, cservice);
			ps.setString(6, note);
			ps.setString(7, ordertime);
			ps.setString(8, cmobile);
			ps.setString(9, ctel);
			ps.setString(10, caddress);
			ps.setString(11, recorder);
			ps.executeUpdate();
			Customer.addServNum(conn, cid);
			conn.commit();
			retval = true;
			logger.info("task : " + cname + " added");
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) { ; }
			retval = false;
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	//load a task info from DB by taskid
	public static JSONObject getTaskByID(String taskid) {
		JSONObject task = new JSONObject();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int tid;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select * from tasks where taskid=?");
			tid = Integer.parseInt(taskid);
			ps.setInt(1, tid);
			rs = ps.executeQuery();
			if (rs.next()) {
				task.put("taskid", rs.getString("taskid"));
				task.put("cid", rs.getString("cid").trim());
				task.put("cname", rs.getString("cname").trim());
				task.put("csex", rs.getString("csex"));
				task.put("cservice", rs.getString("cservice"));
				task.put("sname", rs.getString("sname").trim());
				task.put("consume", rs.getString("consume")==null?0:rs.getString("consume").trim());
				task.put("cash", rs.getString("cash")==null?0:rs.getString("cash").trim());
				task.put("rectime", DBUtil.TIME_FORMAT.format(rs.getTimestamp("rectime")));
				task.put("assigntime", DBUtil.TIME_FORMAT.format(rs.getTimestamp("assigntime")));
				task.put("picktime", DBUtil.TIME_FORMAT.format(rs.getTimestamp("picktime")));
				task.put("finishtime", DBUtil.TIME_FORMAT.format(rs.getTimestamp("finishtime")));
			}
		} catch (Exception e) {
			task = null;
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return task;
	}

	public static boolean deleteTaskByID(String taskid) {
		int rownum = DBUtil.executeUpdate("delete from tasks where taskid='" + taskid + "'");
		
		if (rownum == 1)
			logger.info("task #" + taskid + " deleted");

		return (rownum == 1);
	}

	public String getTaskid() {
		return taskid_;
	}

	public String getCid() {
		return cid_;
	}

	public String getCname() {
		return cname_;
	}

	public String getCtype() {
		return ctype_;
	}

	public String getCsex() {
		return csex_;
	}

	public String getCservice() {
		return cservice_;
	}

	public String getSname() {
		return sname_;
	}

	public String getSmobile() {
		return smobile_;
	}

	public String getComid() {
		return company_;
	}

	public String getState() {
		return state_;
	}

	public int getConsume() {
		return consume_;
	}

	public int getCash() {
		return cash_;
	}
}